package dao;

import gui.GUIClient;
import gui.GUIController;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FileDao {

    // 初始化数据库,也就是建表
    public void initDB(){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            // 1)能够读取到db.sql中的SQL语句
            String[] sqlList = getInitSQL();
            // 2)根据SQL语句调用JDBC执行操作
            for (String sql: sqlList) {
                //System.out.println("[initSQL] sql = " + sql);
                statement.executeUpdate(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement);
        }
    }

    // 读取到db.sql中的文件内容,文件内容包含一个或多个SQL语句,故返回值为字符串
    public String[] getInitSQL(){
        StringBuilder stringBuilder = new StringBuilder();
        // 动态获取到指定文件db.sql的文件路径,而不静态获取
        // 先得到这个类对象,再获取到该类对象对应的类加载器,通过下述方式可以打开Resources里的指定文件,最终返回一个字节流对象
        try (InputStream inputStream = FileDao.class.getClassLoader().getResourceAsStream("db.sql")){
            // 因 db.sql 中的内容为SQL语句,属于文本内容,故需将字节流对象转换为字符流对象
            try(InputStreamReader inputStreamReader = new InputStreamReader(inputStream,"utf8")){
                while(true){
                    int ch = inputStreamReader.read();
                    if(ch == -1) break;
                    stringBuilder.append((char)ch);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return stringBuilder.toString().split(";");
    }

    // 批量插入文件/目录数据到数据库中
    public void add(List<FileMeta> fileMetaList){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 与数据库建立连接
            connection = DBUtil.getConnection();
            // 关闭连接的自动提交功能
            connection.setAutoCommit(false);
            // 构造批量的SQL语句
            String sql = "insert into file_meta values(null,?,?,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            for (FileMeta fileMate: fileMetaList) {
                // 针对当前FileMeta对象,替换SQL语句中的？为具体数值
                statement.setString(1, fileMate.getName());
                statement.setString(2, fileMate.getPath());
                statement.setBoolean(3,fileMate.getIsDirectory());
                statement.setString(4, fileMate.getPinyin());
                statement.setString(5, fileMate.getPinyinFirst());
                statement.setLong(6,fileMate.getSize());
                statement.setTimestamp(7,new Timestamp(fileMate.getLastModified()));
                // 使用 addBatch 将这个构造好的片段累计起来,addBatch 会把上面构造好的SQL保存，同时允许重新构造新的SQL
                statement.addBatch();
                System.out.println("[insert] 插入文件：" + fileMate.getPath() + File.separator + fileMate.getName());
            }
            // 执行所有的SQL片段
            statement.executeBatch();
            // 执行完毕后，通过 commit 告知数据库添加完毕
            connection.commit();
        } catch (SQLException e) {
            // 如果上述代码出现异常，需要进行回滚操作
            try{
                if(connection != null){
                    connection.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtil.close(connection,statement);
        }
    }

    // 按照特定的关键词进行查询，实现文件搜索功能
    // pattern关键词可以是文件名的一部分,文件名拼音的一部分,文件名拼音首字母的一部分...
    public List<FileMeta> searchByPattern(String pattern){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<FileMeta> fileMetaList = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select name,path,is_directory,size,last_modified from file_meta "
                    + "where name like ? or pinyin like ? or pinyin_first like ? "
                    + "order by path,name";
            statement = connection.prepareStatement(sql);
            statement.setString(1,"%" + pattern + "%");
            statement.setString(2,"%" + pattern + "%");
            statement.setString(3,"%" + pattern + "%");
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                String path = resultSet.getString("path");
                Boolean isDirectory = resultSet.getBoolean("is_directory");
                Long size = resultSet.getLong("size");
                Timestamp lastModified = resultSet.getTimestamp("last_modified");
                FileMeta fileMeta = new FileMeta(name,path,isDirectory,size,lastModified.getTime());
                fileMetaList.add(fileMeta);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fileMetaList;
    }

    public List<FileMeta> searchByPathAndPattern(String pattern,String absolutePath){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<FileMeta> fileMetaList = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select name,path,is_directory,size,last_modified from file_meta "
                    + "where (name like ? or pinyin like ? or pinyin_first like ?) and (path = ?) "
                    + "order by path,name";
            statement = connection.prepareStatement(sql);
            statement.setString(1,"%" + pattern + "%");
            statement.setString(2,"%" + pattern + "%");
            statement.setString(3,"%" + pattern + "%");
            statement.setString(4, absolutePath);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                String path = resultSet.getString("path");
                Boolean isDirectory = resultSet.getBoolean("is_directory");
                Long size = resultSet.getLong("size");
                Timestamp lastModified = resultSet.getTimestamp("last_modified");
                FileMeta fileMeta = new FileMeta(name,path,isDirectory,size,lastModified.getTime());
                fileMetaList.add(fileMeta);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fileMetaList;
    }

    // 给定路径，查询这个路径对应的结果，也就是该路径下的文件或目录数据
    // 该方法会在后续重新扫描，更新数据库的时候使用
    public List<FileMeta> searchByPath(String targetPath){
        List<FileMeta> fileMetaList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select name,path,is_directory,size,last_modified from file_meta "
                    + "where path = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,targetPath);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                String path = resultSet.getString("path");
                Boolean isDirectory = resultSet.getBoolean("is_directory");
                Long size = resultSet.getLong("size");
                Timestamp lastModified = resultSet.getTimestamp("last_modified");
                FileMeta fileMeta = new FileMeta(name,path,isDirectory,size,lastModified.getTime());
                fileMetaList.add(fileMeta);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return fileMetaList;
    }

    // 删除数据,若文件已经从磁盘中删除，就需要对数据表中的内容进行更新
    // 如果删除的是普通文件，就直接把对应的表记录删除.如果删除的为文件目录，就需要把该目录下对应的子文件和子目录都删除
    public void delete(List<FileMeta> fileMetaList){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 与数据库建立连接
            connection = DBUtil.getConnection();
            // 关闭连接的自动提交功能
            connection.setAutoCommit(false);

            for (FileMeta fileMeta: fileMetaList) {
                String sql = null;
                if(!fileMeta.getIsDirectory()){
                    // 删除的为普通文件
                    sql = "delete from file_meta where name = ? and path = ?";
                }else{
                    // 删除的为文件目录
                    sql = "delete from file_meta where (name = ? and path = ?) or (path like ?)";
                }

                statement = connection.prepareStatement(sql);
                if(!fileMeta.getIsDirectory()){
                    statement.setString(1, fileMeta.getName());
                    statement.setString(2, fileMeta.getPath());
                }else{
                    statement.setString(1, fileMeta.getName());
                    statement.setString(2, fileMeta.getPath());
                    statement.setString(3, fileMeta.getPath() + File.separator + fileMeta.getName() + File.separator+ "%");
                }
                // SQL语句的最终执行
                statement.executeUpdate();
                // 日志信息的打印
                System.out.println("[delete] " + fileMeta.getPath() + fileMeta.getName());
                // 此处代码中有多个statement对象，而每个对象都需要关闭一次
                statement.close();
            }
            // 告知事务已经执行完毕
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtil.close(connection,statement);
        }
    }
}
